---
title: "SQL-PREL-01: Child relationships of a parent location"
summary: Retrieve child provider locations related to a parent location
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PREL-01 Child relationships of a parent location

Retrieves the child provider locations related to a given parent provider location through
`PROVIDER_RELATIONSHIP`, returning the child location's full detail columns and the
relationship's effective/termination dates and code.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PREL-01 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProviderRelationshipCollection` |
| **Criteria property** | `RetrieveChildRelationshipsSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ProviderLocationID` | Input | Decimal | N/A | Parent location key; returns child relationships |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | JOIN | `PROVIDER_LOCATION_SEQ_NUM` and full detail columns (`APPROVED_FLAG`, `BUSINESS_NAME`, `BUSINESS_FLAG`, etc.) |
| [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship) | JOIN | `PROVIDER_LOCATION_SEQ_NUM`, `PARENT_PROVIDER_LOC_SEQ_NUM`, `EFF_DATE`, `EXP_DATE`, `PROVIDER_RELATIONSHIP_CODE` |

## Sample SQL

```sql
-- Child relationships of a parent location
SELECT
  PR.Provider_Location_Seq_Num     AS PRProviderLocationID,
  PR.PARENT_PROVIDER_LOC_SEQ_NUM   AS ParentProviderLocationID,
  PR.EFF_DATE                      AS StartDate,
  PR.EXP_DATE                      AS TerminationDate,
  PR.PROVIDER_RELATIONSHIP_CODE    AS ProviderRelationshipCode,
  PL.APPROVED_FLAG                 AS IsApproved,
  PL.APPROVED_USER                 AS ApprovedUser,
  PL.BUSINESS_FLAG                 AS IsBusiness,
  PL.CRITICAL_ACCESS_HOSPITAL_FLAG AS IsCriticalAccessHospital,
  PL.BUSINESS_NAME                 AS BusinessName,
  PL.FAX_NUM                       AS FaxNum,
  PL.FIRST_NAME                    AS FirstName,
  PL.INELIGIBILITY_FLAG            AS IsIneligible,
  PL.LAST_NAME                     AS LastName,
  PL.MIDDLE_NAME                   AS MiddleName,
  PL.PAYEE_NAME                    AS PayeeName,
  PL.PHONE_AREA_CODE               AS PhoneAreaCode,
  PL.PHONE_NUM                     AS PhoneNum,
  PL.PHONE_NUM_EXT                 AS PhoneNumExt,
  PL.POSSIBLE_FRAUD_FLAG           AS IsPossibleFraud,
  PL.PRIMARY_LOCATION_FLAG         AS IsPrimaryLocation,
  PL.PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  PL.PROVIDER_SEQ_NUM              AS ProviderID,
  PL.PROVIDER_TYPE_CODE            AS ProviderTypeCode,
  PL.RECENT_ACTIVITY_FLAG          AS HasRecentActivity,
  PL.TOLL_FREE_FAX_NUM             AS TollFreeFaxNum,
  PL.TOLL_FREE_PHONE_NUM           AS TollFreePhoneNum,
  PL.UPDATE_DATE                   AS UpdateDate,
  PL.UPDATE_NUM                    AS UpdateNum,
  PL.UPDATE_USER                   AS UpdateUser,
  PL.WEB_SITE_ADDRESS              AS WebSiteAddress,
  PL.EMAIL_ADDR                    AS EmailAddress
FROM provider_location pl
  JOIN provider_relationship pr
    ON pr.provider_location_seq_num = PL.provider_location_seq_num
  LEFT JOIN provider_location parentPL
    ON parentPL.provider_location_seq_num = pr.parent_provider_loc_seq_num
WHERE parentpl.provider_location_seq_num = :ProviderLocationID
ORDER BY PL.BUSINESS_NAME
```

> **Note:** `RetrieveSQL` returns the *child* statement when `SourceRelationshipType` is
> `Parent` (the `Case Else` branch) and the *parent* statement when it is `Child`.*

## Operations and processes
- [Load provider relationships](../operations/load-provider-relationships.md)

## Business rules
- [BR-PREL-001: Works-for relationship code](../business-rules.md#br-prel-001-works-for-relationships-are-wrk-code)
- [BR-PREL-002: Active-only works-for filter](../business-rules.md#br-prel-002-works-for-defaults-to-active-relationships-only)
- [BR-PREL-003: Relationship active window](../business-rules.md#br-prel-003-relationship-active-date-determination)
- [BR-PREL-004: Reciprocal relationship direction](../business-rules.md#br-prel-004-destination-relationship-type-is-the-opposite-of-the-source)

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Sources
- SAIF.Business.View.Provider/clsProviderRelationshipCollection.vb